clear
set mem 300m

capture log close
log using "$dataloc/log/bankscope_metrics", text replace
set more off

/* --------------------------------------------------------------- *
   Author: Ed Al-Hussainy
   Description:  Generate metrics using BankScope data
 * --------------------------------------------------------------- */

forvalues i = 1/10 { 
    tempfile tmp`i' 
}

use "$dataloc/Data/BankScope/$dataver/BankScope", clear

/* --------------------------------------------------------------- *
   filters
 * --------------------------------------------------------------- */
 
* garbage values 
foreach i of var data2000 data2010 data2025 data2030 data2055 data2075 data2080 data2085 data2090 {
   replace `i' = . if `i' <= 0
}  

* drop if banks do not report total assets
drop if data2025 == .

* keep countries that have at least 5 banks reporting each year (based on number of nonmissing obs for total assets)
bysort cncode year: egen bankcount = count(data2025) 
drop if bankcount < 5 

/* --------------------------------------------------------------- *
   concentration
 * --------------------------------------------------------------- */

* ASSET CONCENTRATION
gsort +cncode +year -data2025
by cncode year: egen _t1a = rank(-data2025)
by cncode year: egen _t1b = sum(data2025) if _t1a <= 3
by cncode year: egen _t1c = sum(data2025)
by cncode year: gen S01BSK0 = (_t1b / _t1c) if _n == 1 & bankcount > 3

/* --------------------------------------------------------------- *
   collapse to system-level for FinStats indicators
 * --------------------------------------------------------------- */

* collapse (sum) data* (mean) S01BSK0, by(cncode year) 

/* --------------------------------------------------------------- *
   indicators
 * --------------------------------------------------------------- */

* NET INTEREST MARGIN
gen S02BSK0 = (data2080 / data2010)

* OVERHEADS / TOTAL ASSETS
gen S03BSK0 = (data2090 / data2025)

* COST TO INCOME RATIO
egen ti = rowtotal(data2085 data2080)
gen S04BSK0 = data2090 / ti

* ROAA
bysort cncode (year): gen aa = (data2025 + data2025[_n-1])/2
gen S05BSK0 = (data2115 / aa)

* ROAE
bysort cncode (year): gen ae = (data2055 + data2055[_n-1])/2
gen S06BSK0 = (data2115 / ae)

* NON-INTEREST INCOME / TOTAL INCOME
gen S07BSK0 = data2085 / ti

* NET LOANS / DEPOSITS & SHORT TERM FUNDING
gen S08BSK0 = (data2000 / data2030)

* LIQUID ASSETS / DEPOSITS & SHORT TERM FUNDING
gen S09BSK0 = (data2075 / data2030)


/* --------------------------------------------------------------- *
    ZSCORE = (ROA+equity/assets)/sd(ROA)
 * --------------------------------------------------------------- */
   
gen roa = S05BSK0

* equity/assets
gen eta = data2055 / data2025  

/* bank-level estimates   

* each (bank,year) obs should have at least 4 preceding obs for the same bank
bysort cncode indexnumber: egen minyr = min(year)
bysort cncode indexnumber: gen _t0 = year - minyr
drop if _t0 < 5

gen sd_roa = .
gen mean_eta = .

forvalues i = 1994/2009 {  
   local t1 = `i'
   local t0 = `i' - 4
   di "`t0' `t1'"
   bysort cncode indexnumber: egen _t1 = sd(roa) if year >= `t0' & year <= `t1'
   bysort cncode indexnumber: egen _t2 = mean(eta) if year >= `t0' & year <= `t1'
   replace sd_roa = _t1 if year == `t1'
   replace mean_eta = _t2 if year == `t1'
   drop _t*
}      

gen zscore = (roa + mean_eta)/sd_roa
*/


* system-level estimates
collapse (mean) roa eta S*, by(cncode year)

gen sd_roa = .
gen mean_eta = .

forvalues i = 1994/2009 {  
   local t1 = `i'
   local t0 = `i' - 4
   di "`t0' `t1'"
   bysort cncode: egen _t1 = sd(roa) if year >= `t0' & year <= `t1'
   bysort cncode: egen _t2 = mean(eta) if year >= `t0' & year <= `t1'
   replace sd_roa = _t1 if year == `t1'
   replace mean_eta = _t2 if year == `t1'
   drop _t*
}    

gen S10BSK0 = (roa + mean_eta)/sd_roa

replace S10BSK0 = . if S10BSK0 > 100

keep cncode year S*

/* --------------------------------------------------------------- *
    var labels
 * --------------------------------------------------------------- */

label var S01BSK0 "3 BANK ASSET CONCENTRATION"
label var S02BSK0 "NET INTEREST MARGIN"
label var S03BSK0 "OVERHEADS / TOTAL ASSETS"
label var S04BSK0 "COST TO INCOME RATIO"
label var S05BSK0 "RETURN ON ASSETS"
label var S06BSK0 "RETURN ON EQUITY"
label var S07BSK0 "NON-INTEREST INCOME / TOTAL INCOME"
label var S08BSK0 "NET LOANS / DEPOSITS & SHORT TERM FUNDING"
label var S09BSK0 "LIQUID ASSETS / DEPOSITS & SHORT TERM FUNDING"
label var S10BSK0 "Z-SCORE"

sum S*

/*

FinStats Tier1 vars:
Liquid Assets / Deposits & Short Term Funding         
3-Bank Asset Concentration          
Net Interest Margin        
Non-Interest Income / Total income        
Return on Assets        
Return on Equity        
Overhead Costs / Total Assets       
Cost to Income Ratio       

Structure DB vars:
overhead
netintmargin
concentration
roa
roe
costinc
zscore
*/


/* --------------------------------------------------------------- *
    save results
 * --------------------------------------------------------------- */

keep cncode year S*

drop if year < $startyear 
drop if year > $endyear

isid cncode year
sort cncode year

compress 
 
label data "BankScope Metrics (Version: $dataver)"
save "$dataloc/Output/BSKmetrics", replace

/* end of code */
clear
log close
exit

